package com.zts.modules.sys.mapper;

import com.google.common.base.Strings;
import com.zts.modules.sys.entity.Group;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;
import java.util.Map;

/**
 * Created by lily on 2017/6/7.
 */
public class GroupSqlProvider {
    /**
     * 根据主键id获取对象
     *
     * @param id
     * @return
     */
    public String get(final Long id) {
        return new SQL() {
            {
                SELECT("g.*");
                FROM(" sys_group g");
                WHERE(" g.id = #{id} ");
            }
        }.toString();
    }

    /**
     * 根据查询条件获取对象list
     *
     * @param group
     * @return
     */
    public String getByEntity(final Group group) {
        return new SQL() {
            {
                SELECT("g.*");
                FROM("sys_group g");
                //通过条件 判断是否需要连接该字段
                if (!Strings.isNullOrEmpty(group.getGroupName())) {
                    WHERE("g.group_name = #{groupName}");
                }
                if (null != group.getId() && group.getId() > 0) {
                    WHERE("g.id  = #{id}");
                }

                ORDER_BY(" g.id ");
            }
        }.toString();
    }

    /**
     * 根据条件进行查询，返回list
     *
     * @param group
     * @return
     */
    public String getList(final Group group) {
        return new SQL() {
            {
                SELECT("g.*");
                FROM("sys_group g");
                if (group.getId() != null && group.getId() > 0) {
                    WHERE("g.id = #{id}");
                }
                if (!Strings.isNullOrEmpty(group.getGroupName())) {
                    WHERE("g.group_name like '%'||#{groupName}||'%'");
                }
                if (!Strings.isNullOrEmpty(group.getType())) {
                    WHERE("g.type=#{type}");
                }
                if (!Strings.isNullOrEmpty(group.getStatus())) {
                    WHERE("g.status=#{status}");
                }
                if (null != group.getParentId()) {
                    WHERE("g.parentid = #{parentId}");
                }
                if (null != group.getCreateTime()) {
                    WHERE("g.create_time >= #{createTime,jdbcType=DATE}");
                }
                if (null != group.getUpdateTime()) {
                    WHERE("g.update_time >= #{updateTime,jdbcType=DATE}");
                }
                if (!Strings.isNullOrEmpty(group.getRemark())) {
                    WHERE("g.remark like '%'||#{remark}||'%'");
                }
                if(group.getSortNum() > 0){
                    WHERE("g.sort_num = #{sortNum}");
                }

                ORDER_BY(" g.id");
            }
        }.toString();
    }

    /**
     * 修改保存
     *
     * @param group
     * @return
     */
    public String update(final Group group) {
        return new SQL() {
            {
                UPDATE("sys_group");
                if (!Strings.isNullOrEmpty(group.getGroupName())) {
                    SET("group_name = #{groupName}");
                }
                if (!Strings.isNullOrEmpty(group.getType())) {
                    SET("type = #{type}");
                }
                if (!Strings.isNullOrEmpty(group.getStatus())) {
                    SET("status = #{status}");
                }

                if (null != group.getParentId()) {
                    SET("parentid = #{parentId}");
                }
                if (!Strings.isNullOrEmpty(group.getRemark())) {
                    SET("remark = #{remark}");
                }
                if(group.getSortNum() > 0){
                    SET("sort_num = #{sortNum}");
                }
                SET("update_time = now()");
                WHERE("id = #{id}");
            }
        }.toString();
    }

    /**
     * 新增一条记录
     *
     * @param group
     * @return
     */
    public String insert(final Group group) {
        return new SQL() {
            {
                INSERT_INTO("sys_group");
                VALUES("group_name", "#{groupName}");
                VALUES("type", "#{type}");
                if (!Strings.isNullOrEmpty(group.getStatus())) {
                    VALUES("status", "#{status}");
                } else {
                    VALUES("status", "1");//默认有效
                }
                if (null != group.getParentId() && group.getParentId() > 0) {
                    VALUES("parentid", "#{parentId}");
                }
                if (!Strings.isNullOrEmpty(group.getRemark())) {
                    VALUES("remark", "#{remark}");
                }
                if (null != group.getCreateTime()) {
                    VALUES("create_time", "#{createTime}");
                } else {
                    VALUES("create_time", "now()");
                }
                if(group.getSortNum() > 0){
                    VALUES("sort_num","#{sortNum}");
                }

            }
        }.toString();
    }

    /**
     * 删除（status置为无效）
     *
     * @param group
     * @return
     */
    public String delete(final Group group) {
        return new SQL() {
            {
                UPDATE("sys_group");
                SET("status = '0'");
                SET("update_time = now()");
                WHERE("id = #{id}");
            }
        }.toString();
    }

    /**
     * 查询下级组（直接下级或间接下级）
     */
    public String getChildren(final Map<String, Object> param) {
        if (!Strings.isNullOrEmpty(param.get("ifDirect").toString()) && "true".equals(param.get("ifDirect").toString())) {
            //查询直接下级组
            return new SQL() {
                {
                    SELECT("g.*");
                    FROM("sys_group g");
                    WHERE("g.parentid = #{id}");
                }
            }.toString();
        } else {
            //查询包括间接下级组在内的所有下级组
            StringBuffer sql = new StringBuffer("");
            sql.append("WITH RECURSIVE r AS");
            sql.append("    (select * from sys_group where  parentid=" + param.get("id"));
            sql.append("     union");
            sql.append("     select g.* from sys_group g join r on g.parentid = r.id )");
            sql.append(" select * from r ");
            return sql.toString();
        }
    }

    /**
     * 查询组下的用户（直接用户或间接用户）
     *
     * @param param
     * @return
     */
    public String getUsers(final Map<String, Object> param) {

        if (!Strings.isNullOrEmpty(param.get("ifDirect").toString()) && "true".equals(param.get("ifDirect").toString())) {
            //获取直接用户
            return new SQL() {
                {
                    SELECT("u.*");
                    FROM("sys_user u");
                    WHERE("exists (select * from sys_user_group r where r.group_Id = " + param.get("id") + "  and u.id = r.user_id)");
                    ORDER_BY("u.id");
                }
            }.toString();
        } else {
            //返回包括间接关系的组的所有下级组下面的用户
            StringBuffer sql = new StringBuffer("");
            sql.append("WITH RECURSIVE r AS");
            sql.append("    (select * from sys_group where  id=" + param.get("id"));
            sql.append("     union");
            sql.append("     select g.* from sys_group g join r on g.parentid = r.id )");
            sql.append("  select u.* from sys_user u  ");
            sql.append("  join sys_user_group ugr on u.id = ugr.user_id");
            sql.append("  join r on r.id = ugr.group_id ");
            return sql.toString();
        }
    }

    /**
     * 将某个用户加入某个组中
     *
     * @param param
     * @return
     */
    public String addUserIntoGroup(final Map<String, Object> param) {
        return new SQL() {
            {
                INSERT_INTO("sys_user_group");
                VALUES("user_id", "#{userId}");
                VALUES("group_id", "#{groupId}");
                VALUES("group_type", "#{groupType}");
                if(null != param.get("sortNum") && ((Integer)param.get("sortNum")).intValue() > 0){
                    VALUES("sort_num","#{sortNum}");
                }
            }
        }.toString();
    }

    /**
     * 批量将多个用户加入某个组中
     *
     * @param param
     * @return
     */
    public String addUsersIntoGroup(final Map<String, Object> param) {
        StringBuffer sql = new StringBuffer("insert into sys_user_group(user_id,group_id,group_type) values ");
        List<Long> userIds = (List<Long>) param.get("userIds");
        //便利userIds，组装sql
        if (null != userIds && userIds.size() > 0) {
            for (int i = 0; i < userIds.size(); i++) {
                if (i < userIds.size() - 1) {
                    sql.append("(").append(userIds.get(i)).append(",").append(param.get("groupId")).append(",");
                    sql.append("'").append(param.get("groupType").toString()).append("'").append("),");
                } else {
                    sql.append("(").append(userIds.get(i)).append(",").append(param.get("groupId")).append(",");
                    sql.append("'").append(param.get("groupType").toString()).append("'").append(")");
                }
            }
        }
        return sql.toString();
    }

    /**
     * 用户user是否在组group中
     *
     * @param param
     * @return
     */
    public String checkUserGroupExist(final Map<String, Object> param) {
        return new SQL() {
            {
                SELECT(" case when count(1) >0 then true else false end ifExist ");
                FROM("sys_user_group");
                WHERE("user_id = #{userId}");
                WHERE("group_id = #{groupId}");
            }
        }.toString();
    }

    /**
     * 将用户从组中删除
     *
     * @param param
     * @return
     */
    public String deleteUserFromGroup(final Map<String, Object> param) {
        return new SQL() {
            {
                DELETE_FROM("sys_user_group");
                WHERE("user_id = #{userId}");
                WHERE("group_id = #{groupId}");
            }
        }.toString();
    }

    /**
     * 批量将多个用户从组中删除
     *
     * @param param
     * @return
     */
    public String deleteUsersFromGroup(final Map<String, Object> param) {
        //组装userId的in语句
        List<Long> userIds = (List<Long>) param.get("userIds");
        String inStr = "";
        if (null != userIds && userIds.size() > 0) {
            for (int i = 0; i < userIds.size(); i++) {
                inStr = inStr + userIds.get(i) + ",";
            }
            inStr = inStr.substring(0, inStr.length() - 1);
        }
        StringBuffer sql = new StringBuffer("");
        sql.append("delete from sys_user_group where 1=1 ");
        if (Strings.isNullOrEmpty(inStr)) {
            sql.append("and user_id is null");//不删除数据
        } else {
            sql.append(" and user_id in (" + inStr + ") ");
        }
        sql.append(" and group_id = " + param.get("groupId"));

        return sql.toString();
    }


}
